from sqlalchemy import Column, Integer, String, Text, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from ..base import Base


class AttributeDefinition(Base):
    """
    商品属性定义表 - 用于定义商品的属性类型（如颜色、尺寸、容量等）
    符合第三范式：属性定义与属性值分离
    """
    __tablename__ = "product_attribute_definitions"

    id = Column(Integer, primary_key=True, index=True, comment="属性定义ID")
    name = Column(String(100), nullable=False, unique=True, comment="属性名称")
    code = Column(String(50), nullable=False, unique=True, comment="属性代码（如color, size, capacity）")
    description = Column(Text, nullable=True, comment="属性描述")
    input_type = Column(String(20), default="select", comment="输入类型：select, text, number等")
    is_required = Column(Boolean, default=False, comment="是否必填")
    is_filterable = Column(Boolean, default=True, comment="是否可用于筛选")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    # 关联属性值
    values = relationship("AttributeValue", backref="definition", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<AttributeDefinition(id={self.id}, name='{self.name}', code='{self.code}')>"


class AttributeValue(Base):
    """
    商品属性值表 - 用于存储属性的具体值
    符合第三范式：属性值与属性定义分离
    """
    __tablename__ = "product_attribute_values"

    id = Column(Integer, primary_key=True, index=True, comment="属性值ID")
    attribute_id = Column(Integer, ForeignKey("product_attribute_definitions.id", ondelete="CASCADE"), nullable=False, comment="属性定义ID")
    value = Column(String(100), nullable=False, comment="属性值")
    display_name = Column(String(100), nullable=False, comment="显示名称")
    price_adjustment = Column(Integer, default=0, comment="价格调整（分为单位）")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    def __repr__(self):
        return f"<AttributeValue(id={self.id}, attribute_id={self.attribute_id}, value='{self.value}')>"


class ProductAttribute(Base):
    """
    商品属性关联表 - 用于关联商品与属性值
    符合第三范式：商品属性关联与属性定义分离
    """
    __tablename__ = "product_attributes"

    id = Column(Integer, primary_key=True, index=True, comment="商品属性ID")
    product_id = Column(Integer, ForeignKey("products.id", ondelete="CASCADE"), nullable=False, comment="商品ID")
    attribute_id = Column(Integer, ForeignKey("product_attribute_definitions.id", ondelete="CASCADE"), nullable=False, comment="属性定义ID")
    attribute_value_id = Column(Integer, ForeignKey("product_attribute_values.id", ondelete="CASCADE"), nullable=False, comment="属性值ID")
    stock_quantity = Column(Integer, default=0, comment="该属性组合的库存")
    is_default = Column(Boolean, default=False, comment="是否默认选项")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    is_active = Column(Boolean, default=True, comment="是否激活")

    created_at = Column(DateTime(timezone=True), server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), comment="更新时间")

    def __repr__(self):
        return f"<ProductAttribute(id={self.id}, product_id={self.product_id}, attribute_id={self.attribute_id})>"